package com.ktjy.ktmall.dao.impl;

import com.ktjy.ktmall.dao.AttrItemDao;
import com.ktjy.ktmall.dao.BaseDao;
import com.ktjy.ktmall.entity.AttrItem;
import jdk.nashorn.internal.runtime.ListAdapter;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 规格属性DAO接口实现类
 * @version v1.0
 * @Author 郴州科泰
 * @Create 2024-07-24 - 21:06
 */
public class AttrItemDaoImpl extends BaseDao implements AttrItemDao {

    /**
     * 查询所有的规格属性
     */
    @Override
    public List<AttrItem> findAllAttr() {
        List<AttrItem> list = new ArrayList<>();
        String sql = "select * from t_attr";
        rs = super.executeQuery(sql);
        try {
            while (rs.next()) {
                AttrItem a = new AttrItem(rs.getInt("id"),rs.getString("name"), rs.getInt("order_num"),rs.getInt("enabled"),rs.getString("create_time"),rs.getString("update_time"));
                list.add(a);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return list;
    }

    /**
     * 根据商品id查询该商品的所有属性列表
     * @param goodsId 属性id
     * @return 返回该商品的所有属性列表
     */
    @Override
    public List<AttrItem> findAttrByGoodsId(int goodsId) {
        List<AttrItem> list = new ArrayList<>();
        String sql = "select * from t_attr where FIND_IN_SET(`id`, (select GROUP_CONCAT(DISTINCT `attr_id` SEPARATOR ',') from t_attr_values where FIND_IN_SET(id, (select GROUP_CONCAT(attr_value_id SEPARATOR ',') from `t_goods_attr_values` where goods_id = ?))))";
        // select DISTINCT `attr_id` from t_attr_values 到t_attr_values属性值表中查询所有的
        // 属性id，因为属性id有重复的，所以使用DISTINCT进行去重。去重后再将多个属性id借助GROUP_CONCAT()函数将多个属性id使用逗号进行连接。连接后的格式为： '1,2,3'，因为连接后的结果是字符串，所以最后还要借助FIND_IN_SET()函数匹配id的值是否在连接后的字符串中存在。
        rs = super.executeQuery(sql, goodsId);
        try {
            while (rs.next()) {
                AttrItem a = new AttrItem(rs.getInt("id"),rs.getString("name"), rs.getInt("order_num"),rs.getInt("enabled"),rs.getString("create_time"),rs.getString("update_time"));
                list.add(a);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return list;
    }

    /**
     * 根据库存sku查询该sku的所有属性列表
     * @param stockSku 库存sku
     * @return 返回该商品的所有属性列表
     */
    @Override
    public List<AttrItem> findAttrByStockSku(String stockSku) {
        List<AttrItem> list = new ArrayList<>();
        String sql = "select * from t_attr where FIND_IN_SET(`id`, (select GROUP_CONCAT(DISTINCT `attr_id` SEPARATOR ',') from t_attr_values where FIND_IN_SET(id, (select GROUP_CONCAT(attr_value_id SEPARATOR ',') from `t_sku_attr_values` where stock_sku = ?))))";
        rs = super.executeQuery(sql, stockSku);
        try {
            while (rs.next()) {
                AttrItem a = new AttrItem(rs.getInt("id"),rs.getString("name"), rs.getInt("order_num"),rs.getInt("enabled"),rs.getString("create_time"),rs.getString("update_time"));
                list.add(a);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            super.closeAll(rs, pstmt, conn);
        }
        return list;
    }
}
